Go 原生的 SQL 操作

大部分内容转载自数据库连接建立和增删改查基本实现

不同于 HTTP 包，Golang 并没有提供 MySQL 客户端扩展包的官方实现，只是提供了一个抽象的 database/sql 接口，只要第三方数据库客户端实现该接口声明的方法，用户就可以在不同的第三方数据库客户端扩展包实现之间进行切换，而不需要调整任何业务代码。

就像 Java 的 JDBC 提供的接口一样，需要厂商自己提供客户端驱动

官方也提供了 Example 代码，如果不知道咋写，可以看下那个 example_test.go 文件

实现 database/sql 接口的 MySQL 第三方扩展包很多，比较流行的有 go-sql-driver/mysql 和 ORM 扩展包 go-gorm/gorm

下面看看如何通过 go-sql-driver/mysql 在 Go 程序中与 MySQL 数据库交互。

go get -u github.com/go-sql-driver/mysql

初始化数据库

首先创建一个测试表

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE `posts`
(
    `id`      bigint unsigned AUTO_INCREMENT,
    `title`   varchar(100) DEFAULT NULL,
    `content` text,
    `author`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

然后可以在测试代码 db.go 中编写一段 init 方法，在每次代码执行 main 入口函数之前先建立数据库连接：

package db

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

var Db *sql.DB

func init()  {
    var err error
    Db, err = sql.Open("mysql", "root:root@/test_db?charset=utf8mb4&parseTime=true")
    if err != nil {
        panic(err)
    }
}

sql.DB 是一个用于操作数据库的结构体，维护的是一个数据库连接池。数据库连接通过 sql.Open 方法设置，该方法接收一个数据库驱动（这里是 mysql）和数据源名称字符串（按照位置填充即可，更多细节请参考该数据库包的官方文档）：

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

注：如果 MySQL 服务器运行在本地，则 address 字段（IP + 端口号）留空。

成功后返回一个 sql.DB 指针，然后你就可以拿着这个指针操作数据库了。

需要注意的是 Open 方法并没有真正建立连接，也不会对传入的参数做任何验证，它只是负责初始化 sql.DB 结构体字段而已，数据库连接只有在后面真正需要的时候才会建立，是一个懒加载的过程。这样做的好处是提升应用性能，避免不必要的数据库连接开销。

另外，sql.DB 也不需要关闭，sql.DB 维护的是一个连接池，在我们的示例代码中定义了一个全局的 Db 变量来指向它，还可以在创建 sql.DB 后将其传递给要操作数据库的方法。

接下来，我们来看下 Open 方法的参数，第一个参数是数据库驱动，要支持这个驱动需要调用 sql.Register 方法进行注册，由于我们使用了 go-sql-driver/mysql 这个第三方包，这一步是在 mysql 包的 init 方法中完成的（driver.go）：

后面的驱动结构体需要实现 sql 包的 driver.Driver 接口。

当我们通过下面这段代码引入 mysql 包时：

_ "github.com/go-sql-driver/mysql"

就会调用 mysql 包的 init 方法。

Go 官方没有提供任何数据库驱动包，只是在 sql.Driver 中声明了接口，第三方驱动包只要实现这些接口就好了。另外，我们在导入第三方包的时候，需要在前面加上短划线 _，这样做的好处是不会直接使用第三方包中的方法，只能使用官方 database/sql 中提供的方法，当第三方包升级或者需要调整数据库驱动时，不需要修改应用中的代码。

增删改查示例代码

数据库初始化完成并设置好连接配置之后，就可以在 Go 应用中与数据库进行交互了。下面将编写一段对文章表进行增删改查的示例代码来演示 Go 语言中的数据库操作。

简单说明提供的各个方法：

1、Exec() 用于执行 SQL 语句，如果是查询语句，不会返回相关的记录。所以查询语句通常使用 Query() 和 QueryRow()，前者可以返回多条记录，后者只返回一条记录。

2、Exec()、Query()、QueryRow() 接受 1 或多个入参，第一个入参是 SQL 语句，后面的入参是 SQL 语句中的占位符 ? 对应的值，占位符一般用来防 SQL 注入。

3、QueryRow() 的返回值类型是 *sql.Row，row.Scan() 接受 1 或多个指针作为参数，可以获取对应列(column)的值。

定义 Post 结构体

首先我们需要定义一个表示文章表数据结构的结构体：

type Post struct {
    Id int
    Title string
    Content string
    Author string
}

创建新文章

然后我们编写在数据库中创建文章记录的 Create 方法，其实就是在上述全局 Db 数据库连接上执行 SQL 插入语句而已，对应的示例代码如下：

func (post *Post) Create() (err error) {
    // 这个 Prepare 创建一个预处理语句，注意返回的预处理对象需要 Close
    stmt, err := Db.Prepare("insert into posts (title, content, author) values (?, ?, ?)")
    if err != nil {
        panic(err)
    }

    defer stmt.Close()

    res, err := stmt.Exec(post.Title, post.Content, post.Author)
    if err != nil {
        panic(err)
    }
    // 返回自增 ID（因为一般 MySQL 表只能有一个自增 ID），这个不是所有数据库都支持
    postId, _ := res.LastInsertId()
    post.Id = int(postId)
    return
}

这里我们使用了预处理语句，以避免 SQL 注入攻击

实际上，我们还可以通过 stmt.QueryRow(post.Title, post.Content, post.Author) 来执行插入操作，效果是一样的，也可以直接通过 Db.Exec 执行插入操作：

sql := "insert into posts (title, content, author) values (?, ?, ?)"
res, err := Db.Exec(sql, post.Title, post.Content, post.Author)

简单测试：

func main() {
    post := db.Post{Title: "测试标题", Content: "这是一些测试内容", Author: "张三"}
    err := post.Create()
    if err != nil {
        panic(err)
    }

  fmt.Println(post.Id)
}

打印结果

检查数据库

获取单篇文章

创建完文章后，可以通过 Db.QueryRow 执行一条 SQL 查询语句查询单条记录并将结果映射到 Post 结构体中。

func GetPost(id int) (post Post, err error) {
    post = Post{}
    // 这个 Scan 把返回的结果绑定到对应的参数上去
    err = Db.QueryRow("select id, title, content, author from posts where id = ?", id).
        Scan(&post.Id, &post.Title, &post.Content, &post.Author)
    return
}

简单使用

func main() {
    post, err := db.GetPost(1)
    if err != nil {
        return
    }

    fmt.Printf("%v ", post)
}

打印的结果

{1 测试标题 这是一些测试内容 张三}

获取文章列表

我们可以使用 sql.DB 提供的 Query 方法来查询多条文章记录：

func Posts(limit int) (posts []Post, err error) {
    rows, err := Db.Query("select id, title, content, author from posts limit ?", limit)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        post := Post{}
        err = rows.Scan(&post.Id, &post.Title, &post.Content, &post.Author)
        if err != nil {
            panic(err)
        }
        posts = append(posts, post)
    }
    return
}

该方法返回的是 sql.Rows 接口，它是一个迭代器，你可以通过循环调用其 Next 方法返回其中的每个 sql.Row 对象，直到 sql.Rows 中的记录值为空（此时返回 io.EOF）。

在循环体中，我们将每个 sql.Row 对象映射到 Post 对象，再将这个 Post 对象添加到 posts 切片中。

其实对于单条记录，也可以使用类似的方式实现，毕竟单条记录查询是 SELECT 查询的特例：

func GetPost(id int) (post Post, err error) {
    rows, err := Db.Query("select id, title, content, author from posts where id = ? limit 1", id)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        post = Post{}
        err = rows.Scan(&post.Id, &post.Title, &post.Content, &post.Author)
        if err != nil {
            panic(err)
        }
    }
    return
}

当然，前面的 Db.Query 也可以调整为预处理语句实现，只是更繁琐一些：

stmt, err := Db.Prepare("select id, title, content, author from posts limit ?")
if err != nil {
    panic(err)
}
defer stmt.Close()
rows, err := stmt.Query(limit)
if err != nil {
    panic(err)
}
... // 后续其他操作代码

更新文章

对于已存在的文章记录，可以通过执行 SQL 更新语句进行修改：

func (post *Post) Update() (err error)  {
    stmt, err := Db.Prepare("update posts set title = ?, content = ?, author = ? where id = ?")
    if err != nil {
        return
    }
    stmt.Exec(post.Title, post.Content, post.Author, post.Id)
    return
}

当然，也可以通过 stmt.QueryRow 以及 Db.Exec 这两种方式来处理上述操作，使用 Db.Exec 方法更简洁：

func (post *Post) Update() (err error)  {
    _, err = Db.Exec("update posts set title = ?, content = ?, author = ? where id = ?",
        post.Title, post.Content, post.Author, post.Id)
    return
}

Db.Exec 方法返回的是 sql.Result 接口，该接口支持以下两个方法：

我们不需要处理这个 Result 对象，所以通过 _ 将其忽略。

删除文章

删除操作和更新操作类似，只是将 UPDATE 语句调整为 DELETE 语句而已：

func (post *Post) Delete() (err error) {
    stmt, err := Db.Prepare("delete from posts where id = ?")
    if err != nil {
        return
    }
    stmt.Exec(post.Id)
    return
}

当然上述操作可以通过 stmt.QueryRow 或 Db.Exec 方法来实现：

func (post *Post) Delete() (err error) {
    _, err = Db.Exec("delete from posts where id = ?", post.Id)
    return
}

整体测试

最后，我们在 db.go 中编写入口函数 main 测试一下上述数据库增删改查操作是否可以正常运行：

func main()  {
    post := db.Post{Title: "测试标题", Content: "这是一些测试内容", Author: "张三"}
    
    // 创建记录
    post.Create()
    fmt.Println(post)
    
    // 获取单条记录
    dbPost, _ := GetPost(post.Id)
    fmt.Println(dbPost)
    
    // 更新记录
    dbPost.Title = "Golang 数据库操作"
    dbPost.Update()
    
    // 获取文章列表
    posts, _ := Posts(1)
    fmt.Println(posts)
    
    // 删除记录
    dbPost.Delete()
}

Go 操作 MySQL 使用事务

Go 语言中使用以下三个方法实现 MySQL 中的事务操作：

// 开始事务
func (db *DB) Begin() (*Tx, error)
// 回滚事务
func (tx *Tx) Rollback() error
// 提交事务
func (tx *Tx) Commit() error

示例代码：

// 事务更新操作
func transActionUpdate() {
    tx, err := db.Begin()
    if err != nil {
        if tx != nil {
            _ = tx.Rollback()
        }
        fmt.Printf("begin trans action failed, err:%v\n", err)
        return
    }

    sqlStr1 := "UPDATE user SET age = ? WHERE id = ?"
    result1, err := tx.Exec(sqlStr1, 20, 1)
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }

    // RowsAffected 取得改变的行数
    n1, err := result1.RowsAffected()
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec result1.RowsAffected() failed, err:%v\n", err)
        return
    }

    sqlStr2 := "UPDATE user SET age = ? WHERE id = ?"
    result2, err := tx.Exec(sqlStr2, 20, 6)
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }

    n2, err := result2.RowsAffected()
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec result1.RowsAffected() failed, err:%v\n", err)
        return
    }

    if n1 == 1 && n2 == 1 {
        _ = tx.Commit()
        fmt.Printf("transaction commit success\n")
    } else {
        _ = tx.Rollback()
        fmt.Printf("transaction commit error, rollback\n")
        return
    }
}

关联查询

上面的操作大部分是针对单表查询的，实际上实际开发还需要面对以下这种多对多的情况

一对一：一张表的一条记录对应另一张表的一条记录，比如用户表与用户资料表
一对多：一张表的一条记录对应另一张表的多条记录，比如用户表与文章表、文章表与评论表
多对一：一张表的多条记录归属另一张表的一条记录（一对多的逆向操作）
多对多：一张表的多条记录归属另一张表的多条记录，此时仅仅基于两张表的字段已经无法定义这种关联关系，需要借助中间表来定义，比如文章表与标签表往往是这种关联

新建一张评论表 comments：

CREATE TABLE `comments`
(
    `id`      bigint unsigned NOT NULL AUTO_INCREMENT,
    `content` text COLLATE utf8mb4_unicode_ci,
    `author`  varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `post_id` bigint unsigned                        DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY fk_post_id (`post_id`) REFERENCES posts (`id`) ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

这里我们创建了一个外键将 comments 表的 post_id 字段和 posts 表的 id 字段关联起来，并且通过 ON DELETE CASCADE 声明将两张表级联起来：当删除 posts 表中的某条记录时，自动删除 comments 中与之关联的评论记录（如果省略这个声明，则不能直接删除 posts 表中有 comments 关联依赖的记录）。

定义评论相关操作

定义 Comment 结构体及新建评论方法：

package db

import "errors"

type Comment struct {
    Id int
    Content string
    Author string
    Post *Post
}

func (comment *Comment) Create() (err error) {
    if comment.Post == nil {
        err = errors.New("Post not found")
        return
    }

    sql := "insert into comments (content, author, post_id) values (?, ?, ?)"
    res, err := Db.Exec(sql, comment.Content, comment.Author, comment.Post.Id)
    if err != nil {
        return
    }

    commentId, _ := res.LastInsertId()
    comment.Id = int(commentId)
    return
}

在 Comment 中，可以通过 Post *Post 指针引用其所属的文章对象。

迁移文章增删改查代码

将 posts 表增删改查操作拆分到独立的 post.go，并且在 Post 结构体中引入 Comments []Comment 属性存放关联的评论信息：

package db

type Post struct {
    Id int
    Title string
    Content string
    Author string
    Comments []Comment
}

func Posts(limit int) (posts []Post, err error) {
    stmt, err := Db.Prepare("select id, title, content, author from posts limit ?")
    if err != nil {
        panic(err)
    }
    defer stmt.Close()
    rows, err := stmt.Query(limit)
    if err != nil {
        panic(err)
    }
    for rows.Next() {
        post := Post{}
        err = rows.Scan(&post.Id, &post.Title, &post.Content, &post.Author)
        if err != nil {
            panic(err)
        }
        posts = append(posts, post)
    }
    return
}

func GetPost(id int) (post Post, err error) {
    post = Post{}
    err = Db.QueryRow("select id, title, content, author from posts where id = ?", id).
        Scan(&post.Id, &post.Title, &post.Content, &post.Author)

    // 查询与之关联的 comments 记录
    rows, err := Db.Query("select id, content, author from comments where post_id = ?", post.Id)
    for rows.Next() {
        comment := Comment{Post: &post}
        err = rows.Scan(&comment.Id, &comment.Content, &comment.Author)
        if err != nil {
            return
        }
        post.Comments = append(post.Comments, comment)
    }
    rows.Close()
    return
}

func (post *Post) Create() (err error) {
    sql := "insert into posts (title, content, author) values (?, ?, ?)"
    stmt, err := Db.Prepare(sql)
    if err != nil {
        panic(err)
    }
    defer stmt.Close()

    res, err := stmt.Exec(post.Title, post.Content, post.Author)
    if err != nil {
        panic(err)
    }

    postId, _ := res.LastInsertId()
    post.Id = int(postId)
    return
}

func (post *Post) Update() (err error)  {
    stmt, err := Db.Prepare("update posts set title = ?, content = ?, author = ? where id = ?")
    if err != nil {
        return
    }
    stmt.Exec(post.Title, post.Content, post.Author, post.Id)
    return
}

func (post *Post) Delete() (err error) {
    stmt, err := Db.Prepare("delete from posts where id = ?")
    if err != nil {
        return
    }
    stmt.Exec(post.Id)
    return
}

我们在 GetPost 方法中获取单条文章记录后，再通过对应文章 ID 进行数据库查询获取相关评论信息存放到 post 对象的 Comments 属性中，这样就可以通过该属性获取文章的评论数据了。

整体测试代码

最后编写 main.go 测试上述关联查询：

package main

import (
    "fmt"
)

func main()  {
    // 插入文章记录
    post := db.Post{Title: "测试标题", Content: "这是一些测试内容", Author: "张三"}
    post.Create()

    // 插入评论记录
    comment1 := Comment{Content: "测试评论1", Author: "李四", Post: &post}
    comment1.Create()

    comment2 := Comment{Content: "测试评论2", Author: "王五", Post: &post}
    comment2.Create()

    // 查询文章评论信息
    mysqlPost, _ := GetPost(post.Id)
    fmt.Println(mysqlPost)
    fmt.Println(mysqlPost.Comments)
    fmt.Println(mysqlPost.Comments[0].Post)
}

在 Post 和 Comment 结构体中分别通过 Comments 切片（数组指针）和 Post 指针定义两者之间的一对多和多对一关联，然后在查询文章记录的 GetPost 方法中编写通过 Post ID 查询关联 Comment 记录的代码，在创建 Comment 的时候，也要确保对应的 Post 字段不为空，即 post_id 字段不为空，这样就将两者通过代码关联起来了。

连接 SQLite

package main

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, _ := sql.Open("sqlite3", "gee.db")
    defer func() { _ = db.Close() }()
    _, _ = db.Exec("DROP TABLE IF EXISTS User;")
    _, _ = db.Exec("CREATE TABLE User(Name text);")
    result, err := db.Exec("INSERT INTO User(`Name`) values (?), (?)", "Tom", "Sam")

    if err == nil {
        affected, _ := result.RowsAffected() // 取得改变的行数
        log.Println(affected)
    }

    row := db.QueryRow("SELECT Name FROM User LIMIT 1")
    var name string
    if err := row.Scan(&name); err == nil {
        log.Println(name)
    }
}

Reference

Go 语言操作 MySQL 之事务操作数据表之间关联关系和关联查询

初始化数据库​

增删改查示例代码​

定义 Post 结构体​

创建新文章​

获取单篇文章​

获取文章列表​

更新文章​

删除文章​

整体测试​

Go 操作 MySQL 使用事务​

关联查询​

定义评论相关操作​

迁移文章增删改查代码​

整体测试代码​

连接 SQLite​

Reference​